/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashSet;
import java.util.TreeSet;

/**
 *
 * @author sio
 */
public class DAO {

    public static Collection<Dep> getLesDeps() {
        Collection<Dep> maCol = new TreeSet<Dep>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("select distinct departement from medecin;");
            while (rs.next()) {
                Statement req2;
                req2 = con.createStatement();
                ResultSet rs2 = req2.executeQuery("select * from medecin where departement=" + rs.getString("departement") + ";");
                Collection<Med> lesMeds = new TreeSet<Med>();
                while (rs2.next()) {
                    Med m = new Med(rs2.getString("nom"), rs2.getString("prenom"), rs2.getString("adresse"), rs2.getString("tel"),
                            rs2.getString("specialitecomplementaire"), rs2.getString("id"));
                    lesMeds.add(m);
                }
                Dep d = new Dep(rs.getString("departement"), lesMeds);
                maCol.add(d);
            }

        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return maCol;
    }

    public static Collection<Spe> getLesSpes() {
        Collection<Spe> maCol = new TreeSet<Spe>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("select distinct specialitecomplementaire from medecin;");
            while (rs.next()) {
                if (rs.getString("specialitecomplementaire") != null) {

                    Statement req2;
                    req2 = con.createStatement();
                    ResultSet rs2 = req2.executeQuery("select * from medecin where specialitecomplementaire='"
                            + rs.getString("specialitecomplementaire") + "';");
                    Collection<Med> lesMeds = new TreeSet<Med>();
                    while (rs2.next()) {
                        Med m = new Med(rs2.getString("nom"), rs2.getString("prenom"), rs2.getString("adresse"), rs2.getString("tel"),
                                rs2.getString("specialitecomplementaire"), rs2.getString("id"));
                        lesMeds.add(m);
                    }
                    Spe s = new Spe(rs.getString("specialitecomplementaire"), lesMeds);
                    maCol.add(s);
                }
            }

        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return maCol;
    }
}
